﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data;
using System.Data.Sql;
using System.Configuration;
using System.Linq;
using Microsoft.Data.ConnectionUI;

namespace SyncVisionFields
{
    public partial class Form1 : Form
    {
        public   DataTable schema = new DataTable();

        public Form1()
        {
            InitializeComponent();
            
            BindTables();

        }

        private void BindTables()
        {
            List<String> tablesFrom = GetTables();
            List<String> tablesTo = GetTables();
            List<String> tablesFromRel = GetTables();
            List<String> tablesToRel = GetTables();
            tablesFrom.Sort();
            tablesTo.Sort();
            tablesFromRel.Sort();
            tablesToRel.Sort();

            cmbTableFrom.DataSource = tablesFrom;
            cmbTableTo.DataSource = tablesTo;
        }


        public List<string> GetTables()
        {
            List<string> TableNames = new List<string>();

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ToString()))
            {
                connection.Open();
                using (SqlCommand com = new SqlCommand("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", connection))
                {
                    using (SqlDataReader reader = com.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            TableNames.Add((string)reader["TABLE_NAME"]);
                        }
                    }
                }
            }

            return TableNames;
        }

        public List<string> GetColumns(string TableName)
        {

            List<string> Cols = new List<string>();
            string sql = "SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS where table_name='" + TableName + "'";

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ToString()))
            {
                connection.Open();
                using (SqlCommand com = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = com.ExecuteReader())
                    {

                        while (reader.Read())
                        {
                            Cols.Add((string)reader["Column_NAME"]);
                        }
                    }
                }
            }
        
            return Cols;

        }


        private bool ChangeConnectionString(string connStringName, string newValue)
        {
            try
            {
                //CreateXDocument and load configuration file
                XDocument doc = XDocument.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

                //Find all connection strings
                var query1 = from p in doc.Descendants("connectionStrings").Descendants()
                             select p;

                //Go throught each connection string elements find atribute specified by argument and replace its value with newVAlue
                foreach (var child in query1)
                {
                    foreach (var atr in child.Attributes())
                    {
                        if (atr.Name.LocalName == "name" && atr.Value == connStringName)
                            if (atr.NextAttribute != null && atr.NextAttribute.Name == "connectionString")
                                atr.NextAttribute.Value = newValue;
                    }
                }

                doc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);

                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }

        private void btnSetDB_Click(object sender, EventArgs e)
        {
            //WpfApplication1.MainWindow mw = new WpfApplication1.MainWindow();
            //mw.Activate();
            //string conn = mw.Connectionstring;

            DataSource sqlDataSource = new DataSource("MicrosoftSqlServer", "Microsoft SQL Server");
            sqlDataSource.Providers.Add(DataProvider.SqlDataProvider);
            DataConnectionDialog dcd = new DataConnectionDialog();
            dcd.DataSources.Add(sqlDataSource);
            dcd.SelectedDataProvider = DataProvider.SqlDataProvider;
            dcd.SelectedDataSource = sqlDataSource;
            DataConnectionDialog.Show(dcd);

            ChangeConnectionString("Dashpart_Manager.Properties.Settings.vision_liveConnectionString", dcd.ConnectionString.ToString());

            MessageBox.Show("Please close and reopen the application now.  Thank you.");
        }



        private void cmbTableFrom_SelectedIndexChanged(object sender, EventArgs e)
        {

            List<String> cols = GetColumns(cmbTableFrom.SelectedValue.ToString());
            List<String> colsrel = GetColumns(cmbTableFrom.SelectedValue.ToString());
            cols.Sort();
            cmbTableFromColumnRel.DataSource = colsrel;
            cmbTableFromColumn.DataSource = cols;
        }

        private void cmbTableTo_SelectedIndexChanged(object sender, EventArgs e)
        {

            List<String> cols = GetColumns(cmbTableTo.SelectedValue.ToString());
            List<String> colsrel = GetColumns(cmbTableTo.SelectedValue.ToString());
            cols.Sort();
            cmbTableToColmunRel.DataSource = colsrel;
            cmbTableToColumn.DataSource = cols;
        }


        private void btnPreview_Click(object sender, EventArgs e)
        {
           // if (chk2Specific.Checked)
            {
                string proc;
                proc = "Create PROCEDURE [dbo].[" + txtTitle.Text + "]" + Environment.NewLine;
                proc += "@" + cmbTableFromColumnRel.SelectedItem + " varchar(32) " + Environment.NewLine;

                proc += "AS" + Environment.NewLine + "BEGIN" + Environment.NewLine + "SET NOCOUNT ON;" + Environment.NewLine;

                proc += "Update " + cmbTableTo.SelectedItem + Environment.NewLine;
                proc += "SET " + cmbTableToColumn.SelectedItem + " = " + cmbTableFrom.SelectedItem + "." + cmbTableFromColumn.SelectedItem + Environment.NewLine;
                proc += "from " + cmbTableFrom.SelectedItem + Environment.NewLine;
                proc += "where " + cmbTableFrom.SelectedItem + "." + cmbTableFromColumnRel.SelectedItem + " = " + cmbTableTo.SelectedItem + "." + cmbTableToColmunRel.SelectedItem + Environment.NewLine;
                proc += "and " + cmbTableFrom.SelectedItem + "." + cmbTableFromColumnRel.SelectedItem + " = @" + cmbTableFromColumnRel.SelectedItem + Environment.NewLine;
                proc += "Return 0 " + Environment.NewLine + "END";

                txtPreview.Text = proc;
            }
//            else
//            {
//                string ToTable = cmbToInfo.SelectedValue.ToString();
//                string FromTable = cmbFromInfo.SelectedValue.ToString();

//                string proc;
//                proc = "Create PROCEDURE [dbo].[" + txtTitle.Text + "]" + Environment.NewLine;
//                proc += "@" + cmbTableFromColumn.SelectedItem + " varchar(32) " + Environment.NewLine;

//                proc += "AS" + Environment.NewLine + "BEGIN" + Environment.NewLine + "SET NOCOUNT ON;" + Environment.NewLine;

//                proc += "Update " + ToTable + Environment.NewLine;
//                proc += "SET " + cmbTableToColumn.SelectedItem + " = " + FromTable + "." + cmbTableFromColumn.SelectedItem + Environment.NewLine;
//                proc += "from " + FromTable + Environment.NewLine;
//                proc += "where " + cmbTableFrom.SelectedItem + "." + cmbTableFromColumnRel.SelectedItem + " = " + cmbTableTo.SelectedItem + "." + cmbTableToColmunRel.SelectedItem + Environment.NewLine;
//                proc += "and " + cmbTableFrom.SelectedItem + " = @" + cmbTableFromColumnRel.SelectedItem + Environment.NewLine;
//                proc += "Return 0";

//                txtPreview.Text = proc;

////                    ProjectCustomTabFields 
////SET custPECApprover =custPECMentor 
////from EmployeeCustomTabFields, PR
////where EmployeeCustomTabFields.Employee = @EM
////and PR.WBS1 = ProjectCustomTabFields.WBS1
////and PR.WBS2 = ''


//            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Common_Tables ct = new Common_Tables();
            ct.Show();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[1].ToString()))
            {
                connection.Open();
                using (SqlCommand com = new SqlCommand(txtPreview.Text, connection))
                {
                    com.ExecuteNonQuery();
                }
            }
        }

        private void cmbFromInfo_SelectedIndexChanged(object sender, EventArgs e)
        {
            List<String> cols = GetColumns(cmbFromInfo.SelectedValue.ToString());
            cols.Sort();
            cmbTableFromColumn.DataSource = cols;
        }

        private void cmbToInfo_SelectedIndexChanged(object sender, EventArgs e)
        {
            List<String> cols = GetColumns(cmbToInfo.SelectedValue.ToString());
            cols.Sort();

            cmbTableToColumn.DataSource = cols;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            List<InfoCenter> info = new List<InfoCenter>();


            info.Add(new InfoCenter() { InfoName = "", CustomName = "" });
            info.Add(new InfoCenter(){InfoName = "Projects", CustomName = "ProjectCustomTabFields"});
            info.Add(new InfoCenter(){InfoName = "Clients", CustomName = "ClientCustomTabFields"});
            info.Add(new InfoCenter(){InfoName = "Employees", CustomName = "EmployeeCustomTabFields"});
            info.Add(new InfoCenter(){InfoName = "Opportunities", CustomName = "OpportunityCustomTabFields"});
            info.Add(new InfoCenter(){InfoName = "Vendors", CustomName = "VendorCustomTabFields"});
            info.Add(new InfoCenter(){InfoName = "Contacts", CustomName = "ContactCustomTabFields"});

            List<InfoCenter> info2 = new List<InfoCenter>();

            info2.Add(new InfoCenter() { InfoName = "", CustomName = "" });
            info2.Add(new InfoCenter() { InfoName = "Projects", CustomName = "ProjectCustomTabFields" });
            info2.Add(new InfoCenter() { InfoName = "Clients", CustomName = "ClientCustomTabFields" });
            info2.Add(new InfoCenter() { InfoName = "Employees", CustomName = "EmployeeCustomTabFields" });
            info2.Add(new InfoCenter() { InfoName = "Opportunities", CustomName = "OpportunityCustomTabFields" });
            info2.Add(new InfoCenter() { InfoName = "Vendors", CustomName = "VendorCustomTabFields" });
            info2.Add(new InfoCenter() { InfoName = "Contacts", CustomName = "ContactCustomTabFields" });


            cmbFromInfo.DataSource = info;
            cmbFromInfo.DisplayMember = "InfoName";
            cmbFromInfo.ValueMember = "CustomName";

            cmbToInfo.DataSource = info2;
            cmbToInfo.DisplayMember = "InfoName";
            cmbToInfo.ValueMember = "CustomName";

        }

        private void chk2Specific_CheckedChanged(object sender, EventArgs e)
        {

        }



    }
    
}
